package org.jeecg.modules.compilation.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.List;

@Slf4j
public class POIUtils {

    /**
     * @param fileName 文件存放位置
     * @param ishandleAll    是否处理excel表里面所有的页数  1 处理所有页数 0处理指定页数
     * @param numberOfPages  处理excel表里面第几个页号 1就是第一页(0不是第一页)
     * @param result 处理excel表中的所有数据
     * */
    public void creatWorkBook(String fileName , Integer ishandleAll , Integer numberOfPages , List<String> result) {

        log.info("判断文件是否存在...");
        File excelFile = new File(fileName);
        if (!excelFile.exists()) {
            log.warn("指定的Excel文件不存在！");
            return;
        }

        log.info("创建 WorkBook ...");
        String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
        Workbook workbook = null;
        try {
            FileInputStream fileInputStream = new FileInputStream(excelFile);
            if (fileType.equalsIgnoreCase("xls")) {
                workbook = new HSSFWorkbook(fileInputStream); //生成.xls的excel
            } else if (fileType.equalsIgnoreCase("xlsx")) {
                workbook = new XSSFWorkbook(fileInputStream); //生成.xlsx的excel
            } else {
                log.warn("文件格式不对");
                return;
            }
            log.info("开始解析 WorkBook...");
            parseExcel(workbook , ishandleAll, numberOfPages , result);

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return;
    }


    private void parseExcel(Workbook workbook ,  Integer ishandleAll , Integer numberOfPages , List<String> result ) {

        int start = 0;
        int end = workbook.getNumberOfSheets();

        if ( numberOfPages != null ) {
            if (ishandleAll == 1 && (numberOfPages < 0 || numberOfPages > workbook.getNumberOfSheets())) {
                new RuntimeException("请输入正确的页号！");
            }

             start = ishandleAll == 1 ? 0 : numberOfPages - 1;
             end = ishandleAll == 1 ? workbook.getNumberOfSheets() : numberOfPages;
        }


        for ( ; start <  end ; start++) {
            Sheet sheet = workbook.getSheetAt(start); // 获取表格
            log.info("sheet = {}", sheet.getSheetName());

            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }


            Row firstRow = sheet.getRow(sheet.getFirstRowNum()); // 获取第一行，一般是标题
            if (null == firstRow) {
                log.warn("解析Excel失败，在第一行没有读取到任何数据！");
            }

            // 解析每一行的数据，构造数据对象
            int firstRowNum = 0;
            int rowStart = firstRowNum + 1; //标题下面的数据,数据起始行
            int rowEnd = sheet.getPhysicalNumberOfRows();//获取有记录的行数，即：最后有数据的行是第n行，前面有m行是空行没数据，则返回n-m；

            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);
//                log.info("第 {} 行", row.getRowNum());
                if (null == row) {
                    continue;
                }
                String temp = new String();
                //处理Cell
                for (int cellNum = 1; cellNum < 146; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    if (cell != null) {
                        String content = convertCellValueToString(cell);
//                        log.info("第" + row.getRowNum() + "行，第" + cellNum + "列，内容：" + content);
                        temp += cellNum == 1 ?  content : "," + content;
                    }
                }
                result.add(temp);
            }
        }
    }

    private static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String content = null;
        try {
            switch (cell.getCellType()) {
                case NUMERIC:   //数字或者时间
                    Double doubleValue = cell.getNumericCellValue();
                    // 格式化科学计数法，取一位整数
                    DecimalFormat df = new DecimalFormat("0");
                    content = df.format(doubleValue);
                    break;
                case STRING:    //字符串
                    content = cell.getStringCellValue();
                    break;
                case BOOLEAN:   //布尔
                    Boolean booleanValue = cell.getBooleanCellValue();
                    content = booleanValue.toString();
                    break;
                case BLANK:     // 空值
                    break;
                case FORMULA:   // 公式
                    content = cell.getCellFormula();
                    break;
                case ERROR:     // 故障
                    break;
                default:
                    break;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return content;
    }
}
